﻿using System;
using System.Data;
using System.ServiceModel;
using Cinchoo.Core.Xml.Serialization;
using System.Reflection;
using Cinchoo.Core;
using Cinchoo.Core.Diagnostics;
using System.Data.Common;
using System.Threading;
using System.Collections.Generic;
using Cinchoo.Core.ETL;
using System.Linq;
using System.IO;
using Cinchoo.IO;
using Cinchoo.Core.IO;

namespace ChoWorkflowServer.Model
{
    public abstract class ChoDataModelImpl : IChoDataModel, IDisposable
    {
        private static int _taskIdCounter = 0;
        private static int _taskGroupIdCounter = 0;
        private static int _taskScheduleIdCounter = 0;
        private static int _holidayCalIdCounter = 0;
        private static int _userId = 0;
        private static int _roleId = 0;

        public ChoDataModelImpl()
        {
            _taskIdCounter = GetMaxTaskId();
            _taskGroupIdCounter = GetMaxTaskGroupId();
            _taskScheduleIdCounter = GetMaxTaskScheduleId();
            _holidayCalIdCounter = GetMaxHolidayCalendarId();
            _userId = GetMaxUserId();
            _userId = GetMaxRoleId();

            Initialize();
        }

        private void Initialize()
        {
            //Create <NULL> calandar if missing
            ChoTasksDataSet.CHO_HOLIDAY_CALENDARSDataTable holidayCalendarTable = GetAllHolidayCalendars();
            var nullCalendarRow = (from row in holidayCalendarTable.AsEnumerable()
                          where row.Field<string>(holidayCalendarTable.CAL_NAMEColumn.ColumnName) == ChoCalendar.NULL_CALENDAR_NAME
                          select row).FirstOrDefault();

            if (nullCalendarRow == null)
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();
                    IDbTransaction trans = conn.BeginTransaction();

                    try
                    {
                        AddHolidayCalendar(ChoCalendar.NULL_CALENDAR_ID, ChoCalendar.NULL_CALENDAR_NAME, "Null Calendar", ChoUser.SYSTEM_ACCT, Environment.MachineName, conn, trans);
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        ChoTrace.Write(ex);
                        throw;
                    }
                }
            }
        }

        public virtual void Dispose()
        {
        }

        protected abstract int GetMaxTaskId();
        protected abstract int GetMaxTaskGroupId();
        protected abstract int GetMaxTaskScheduleId();
        protected abstract int GetMaxHolidayCalendarId();
        protected abstract int GetMaxUserId();
        protected abstract int GetMaxRoleId();

        public virtual Version GetVersion()
        {
            return Assembly.GetExecutingAssembly().GetName().Version;
        }

        public ChoTasksDataSet.CHO_TASK_GRPSDataTable GetAllTaskGroups()
        {
            try
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    ChoTasksDataSet.CHO_TASK_GRPSDataTable taskGrpsTable = new ChoTasksDataSet.CHO_TASK_GRPSDataTable();
                    taskGrpsTable.Load(GetAllTaskGroups(conn, null));
                    return taskGrpsTable;
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract IDataReader GetAllTaskGroups(IDbConnection conn, IDbTransaction trans);

        public virtual int GetTaskId(string taskName)
        {
            try
            {
                ChoGuard.ArgumentNotNullOrEmpty(taskName, "TaskName");

                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    return GetTaskId(taskName, conn, null);
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract int GetTaskId(string taskName, IDbConnection conn, IDbTransaction trans);

        public virtual bool IsTaskExists(string taskName)
        {
            try
            {
                ChoGuard.ArgumentNotNullOrEmpty(taskName, "TaskName");

                using (IDbConnection conn = Connection)
                {
                    conn.Open();
                    return IsTaskExists(taskName, conn, null);
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract bool IsTaskExists(string taskName, IDbConnection conn, IDbTransaction trans);

        public virtual int AddOrUpdateTask(string taskName, string workflowName, string taskDesc, int taskGroupId, string cmdParameters,
            int? maxNoOfRetry, int? sleepBetweenRetry, int? timeout, string hostName,
            ChoTasksDataSet.CHO_TASK_SCHEDULESDataTable taskScheduleInfo, string modifiedBy, string modifiedByHostName)
        {
            IDbTransaction trans = null;
            try
            {
                ChoGuard.ArgumentNotNullOrEmpty(taskName, "TaskName");
                ChoGuard.ArgumentNotNullOrEmpty(modifiedBy, "ModifiedBy");

                using (IDbConnection conn = Connection)
                {
                    conn.Open();
                    trans = conn.BeginTransaction();

                    int taskId = AddOrUpdateTask(taskName, workflowName, taskDesc, taskGroupId, cmdParameters, maxNoOfRetry, sleepBetweenRetry, timeout, hostName, taskScheduleInfo, modifiedBy, modifiedByHostName, conn, trans);
                    trans.Commit();

                    return taskId;
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }

        protected virtual int GenerateNextTaskId()
        {
            return Interlocked.Increment(ref _taskIdCounter);
        }

        private int AddOrUpdateTask(string taskName, string workflowName, string taskDesc, int taskGroupId, string cmdParameters,
            int? maxNoOfRetry, int? sleepBetweenRetry, int? timeout, string hostName,
            ChoTasksDataSet.CHO_TASK_SCHEDULESDataTable taskScheduleInfo, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            ChoTaskChangeType opType = ChoTaskChangeType.Update;

            if (timeout != null)
                timeout = ChoTaskTimeout.Resolve(timeout.Value);

            int taskId = GetTaskId(taskName);
            if (taskId > 0)
                taskId = UpdateTask(taskId, taskName, workflowName, taskDesc, taskGroupId, cmdParameters,
                    maxNoOfRetry, sleepBetweenRetry, timeout, hostName, modifiedBy, modifiedByHostName, conn, trans);
            else
            {
                opType = ChoTaskChangeType.Add;
                taskId = GenerateNextTaskId();
                taskId = AddTask(taskId, taskName, workflowName, taskDesc, taskGroupId, cmdParameters,
                    maxNoOfRetry, sleepBetweenRetry, timeout, hostName, modifiedBy, modifiedByHostName, conn, trans);
            }

            UpdateSchedule(taskId, taskName, taskGroupId, workflowName, taskScheduleInfo, modifiedBy, modifiedByHostName, conn, trans);

            if (taskScheduleInfo != null && taskScheduleInfo.Rows.Count > 0)
            {
                ChoTaskMgmtCommand cmd = new ChoTaskMgmtCommand()
                {
                    TaskId = taskId,
                    TaskName = taskName,
                    TaskGroupId = taskGroupId,
                    //ScheduleName = row.SCHEDULE_NAME,
                    WorkflowName = workflowName,
                    TaskMgmtType = opType
                };
                ChoDataModelEvents.RaiseTaskChanged(cmd, modifiedByHostName, modifiedBy);
            }

            return taskId;
        }

        protected abstract int AddTask(int taskId, string taskName, string workflowName, string taskDesc, int taskGroupId, string cmdParameters,
            int? maxNoOfRetry, int? sleepBetweenRetry, int? timeout, string hostName, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract int UpdateTask(int taskId, string taskName, string workflowName, string taskDesc, int taskGroupId, string cmdParameters,
            int? maxNoOfRetry, int? sleepBetweenRetry, int? timeout, string hostName, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);

        public virtual void MoveTask(string taskName, int srcTaskGrpId, int destTaskGrpId, string modifiedBy, string modifiedByHostName)
        {
            try
            {
                ChoGuard.ArgumentNotNullOrEmpty(taskName, "TaskName");
                ChoGuard.ArgumentNotNullOrEmpty(modifiedBy, "ModifiedBy");

                using (IDbConnection conn = Connection)
                {
                    conn.Open();
                    IDbTransaction trans = conn.BeginTransaction();

                    if (IsTaskExistsInGroup(taskName, srcTaskGrpId, conn, trans))
                        MoveTask(taskName, destTaskGrpId, modifiedBy, modifiedByHostName, conn, trans);

                    trans.Commit();
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract void MoveTask(string taskName, int destTaskGrpId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract bool IsTaskExistsInGroup(string taskName, int grpId, IDbConnection conn, IDbTransaction trans);

        public virtual int CloneTask(string taskName, string newTaskName, string taskGroupName, string newTaskGroupName, string modifiedBy, string modifiedByHostName)
        {
            int newTaskId = -1;

            try
            {
                ChoGuard.ArgumentNotNullOrEmpty(taskName, "TaskName");
                ChoGuard.ArgumentNotNullOrEmpty(newTaskName, "NewTaskName");
                ChoGuard.ArgumentNotNullOrEmpty(taskGroupName, "TaskGroupName");
                ChoGuard.ArgumentNotNullOrEmpty(newTaskGroupName, "NewTaskGroupName");
                ChoGuard.ArgumentNotNullOrEmpty(modifiedBy, "ModifiedBy");

                using (IDbConnection conn = Connection)
                {
                    conn.Open();
                    IDbTransaction trans = conn.BeginTransaction();

                    if (GetTaskId(newTaskName, conn, trans) > 0)
                        throw new ChoDbException("'{0}' task already exists.".FormatString(taskName));

                    //Check if task group exists
                    if (GetTaskGroupId(taskGroupName, conn, trans) <= 0)
                        throw new ChoDbException("'{0}' task group not exists.".FormatString(taskGroupName));

                    //Check if new task group exists
                    int newTaskGroupId = GetTaskGroupId(newTaskGroupName, conn, trans);
                    if (newTaskGroupId <= 0)
                        throw new ChoDbException("'{0}' task group not exists.".FormatString(newTaskGroupName));

                    ChoTasksDataSet.CHO_TASKSDataTable tasks = GetAllUITasks(taskName, taskGroupName, conn, trans);
                    if (tasks == null || tasks.Rows.Count == 0)
                        throw new ChoDbException("'{0}' task not found in the database.".FormatString(taskName));

                    ChoTasksDataSet.CHO_TASKSRow row = tasks.Rows[0] as ChoTasksDataSet.CHO_TASKSRow;

                    //Clone the task with new task name
                    ChoTasksDataSet.CHO_TASK_SCHEDULESDataTable sch = new ChoTasksDataSet.CHO_TASK_SCHEDULESDataTable();
                    sch.Load(GetTaskSchedules(row.TASK_ID, null, conn, trans));

                    //Mark them as added
                    var z = sch.AsEnumerable().Select((r) => { r.SetAdded(); return r; });
                    foreach (DataRow row1 in sch.Rows)
                        row1.SetAdded();

                    newTaskId = AddOrUpdateTask(newTaskName, row.WORKFLOW_NAME, row.IsTASK_DESCNull() ? null : row.TASK_STATUS_DESC,
                        newTaskGroupId, row.IsCMD_PARAMETERSNull() ? null : row.CMD_PARAMETERS,
                        row.IsNO_OF_RETRYNull() ? null : (int?)row.NO_OF_RETRY,
                        row.IsSLEEP_BETWEEN_RETRYNull() ? null : (int?)row.SLEEP_BETWEEN_RETRY,
                        row.IsTIMEOUTNull() ? null : (int?)row.TIMEOUT,
                        row.IsMACHINE_NAMENull() ? null : row.MACHINE_NAME,
                        sch, modifiedBy, modifiedByHostName, conn, trans);

                    trans.Commit();
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }

            return newTaskId;
        }

        public virtual void DeleteTask(string taskName, string modifiedBy, string modifiedByHostName)
        {
            try
            {
                ChoGuard.ArgumentNotNullOrEmpty(taskName, "TaskName");
                ChoGuard.ArgumentNotNullOrEmpty(modifiedBy, "ModifiedBy");

                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    IDbTransaction trans = conn.BeginTransaction();

                    int taskId = GetTaskId(taskName);

                    ChoTasksDataSet.CHO_TASK_SCHEDULESDataTable taskScheduleInfo = new ChoTasksDataSet.CHO_TASK_SCHEDULESDataTable();
                    taskScheduleInfo.Load(GetTaskSchedules(taskId, null, conn, trans));

                    foreach (ChoTasksDataSet.CHO_TASK_SCHEDULESRow row in taskScheduleInfo.Rows)
                        DeleteTaskSchedule(row[taskScheduleInfo.SCHEDULE_IDColumn, DataRowVersion.Original].CastTo<int>(), modifiedBy, modifiedByHostName, conn, trans);

                    DeleteTask(taskName, modifiedBy, modifiedByHostName, conn, trans);

                    ChoTaskMgmtCommand cmd = new ChoTaskMgmtCommand()
                    {
                        TaskName = taskName,
                        TaskMgmtType = ChoTaskChangeType.Remove
                    };
                    ChoDataModelEvents.RaiseTaskChanged(cmd, modifiedByHostName, modifiedBy);
                    trans.Commit();
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }

        protected abstract void DeleteTask(string taskName, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);

        public virtual ChoTasksDataSet.CHO_TASKSDataTable GetAllUITasks(string taskName, string taskGrpName)
        {
            try
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();
                    return GetAllUITasks(taskName, taskGrpName, conn, null);
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }

        private ChoTasksDataSet.CHO_TASKSDataTable GetAllUITasks(string taskName, string taskGrpName, IDbConnection conn, IDbTransaction trans)
        {
            ChoTasksDataSet.CHO_TASKSDataTable tasksTable = new ChoTasksDataSet.CHO_TASKSDataTable();

            if (taskName.IsNullOrWhiteSpace() && taskGrpName.IsNullOrWhiteSpace())
                tasksTable.Load(GetAllUITasks(conn, null));
            else if (taskGrpName.IsNullOrWhiteSpace())
                tasksTable.Load(GetUITask(taskName, conn, null));
            else if (taskName.IsNullOrWhiteSpace())
            {
                tasksTable.Load(GetAllUITasks(conn, null));

                ChoTasksDataSet.CHO_TASK_GRPSDataTable taskGrpsTable = new ChoTasksDataSet.CHO_TASK_GRPSDataTable();
                taskGrpsTable.Load(GetAllTaskGroups(conn, trans));

                return GetTasksForGroup(taskGrpName, taskGrpsTable, tasksTable);
                //tasksTable.Load(GetUITasksForGroup(taskGrpName, conn, null));
            }
            else
                tasksTable.Load(GetUITasks(taskName, taskGrpName, conn, null));

            return tasksTable;
        }

        protected abstract IDataReader GetAllUITasks(IDbConnection conn, IDbTransaction trans);
        protected abstract IDataReader GetUITask(string taskName, IDbConnection conn, IDbTransaction trans);
        protected abstract IDataReader GetUITasksForGroup(string taskGrpName, IDbConnection conn, IDbTransaction trans);
        protected abstract IDataReader GetUITasks(string taskName, string taskGrpName, IDbConnection conn, IDbTransaction trans);

        public virtual ChoTasksDataSet.CHO_TASKSDataTable GetAllTasks(string taskName, string taskGrpName, string hostName, DateTime modifiedDate)
        {
            if (modifiedDate == DateTime.MinValue)
                modifiedDate = ChoDbDateTime.MinValue;

            ChoTasksDataSet.CHO_TASKSDataTable tasksTable = new ChoTasksDataSet.CHO_TASKSDataTable();

            try
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    if (taskName.IsNullOrWhiteSpace() && taskGrpName.IsNullOrWhiteSpace())
                        tasksTable.Load(GetAllTasks(hostName, modifiedDate, conn, null));
                    else if (taskGrpName.IsNullOrWhiteSpace())
                        tasksTable.Load(GetTask(taskName, hostName, modifiedDate, conn, null));
                    else if (taskName.IsNullOrWhiteSpace())
                    {
                        tasksTable.Load(GetAllTasks(hostName, modifiedDate, conn, null));

                        ChoTasksDataSet.CHO_TASK_GRPSDataTable taskGrpsTable = new ChoTasksDataSet.CHO_TASK_GRPSDataTable();
                        taskGrpsTable.Load(GetAllTaskGroups(conn, null));

                        return GetTasksForGroup(taskGrpName, taskGrpsTable, tasksTable);
                        //tasksTable.Load(GetTasksForGroup(taskGrpName, hostName, modifiedDate, conn, null));
                    }
                    else
                        tasksTable.Load(GetTasks(taskName, taskGrpName, hostName, modifiedDate, conn, null));
                }

                return tasksTable;
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }

        public class NodeData
        {
            public ChoTasksDataSet.CHO_TASKSRow TaskRow { get; set; }
            public IEnumerable<NodeData> Children { get; set; }
        }

        private ChoTasksDataSet.CHO_TASKSDataTable GetTasksForGroup(string taskGrpName, ChoTasksDataSet.CHO_TASK_GRPSDataTable taskGroups, ChoTasksDataSet.CHO_TASKSDataTable allTasks)
        {
            var results = (from r in taskGroups.AsEnumerable()
                          where r.Field<string>("TASK_GRP_NAME") == taskGrpName
                          select r).FirstOrDefault();

            List<int> taskGroupIds = new List<int>();
            if (results != null)
            {
                int taskGrpId = results.TASK_GRP_ID;
                taskGroupIds.Add(taskGrpId);

                var lookup = taskGroups.AsEnumerable().ToLookup(p => p.Field<int?>("PARENT"));

                Func<IEnumerable<ChoTasksDataSet.CHO_TASK_GRPSRow>, IEnumerable<ChoTasksDataSet.CHO_TASK_GRPSRow>> recurse = null;
                recurse = qs =>
                {
                    return
                        qs
                            .Concat(
                                from q in qs
                                from q2 in recurse(lookup[q.TASK_GRP_ID])
                                select q2);
                };
                foreach (ChoTasksDataSet.CHO_TASK_GRPSRow tgr in recurse(lookup[taskGrpId]).ToArray())
                    taskGroupIds.Add(tgr.TASK_GRP_ID);
            }

            ChoTasksDataSet.CHO_TASKSDataTable tasksForGroup = new ChoTasksDataSet.CHO_TASKSDataTable();
            foreach (int tgr in taskGroupIds)
            {
                foreach (ChoTasksDataSet.CHO_TASKSRow tr in (from r in allTasks.AsEnumerable()
                                                            where r.Field<int>("TASK_GRP_ID") == tgr
                                                             select r))
                {
                    tasksForGroup.ImportRow(tr);
                }
            }
            return tasksForGroup;
        }

        protected abstract IDataReader GetAllTasks(string hostName, DateTime modifiedDate, IDbConnection conn, IDbTransaction trans);
        protected abstract IDataReader GetTask(string taskName, string hostName, DateTime modifiedDate, IDbConnection conn, IDbTransaction trans);
        protected abstract IDataReader GetTasksForGroup(string taskGrpName, string hostName, DateTime modifiedDate, IDbConnection conn, IDbTransaction trans);
        protected abstract IDataReader GetTasks(string taskName, string taskGrpName, string hostName, DateTime modifiedDate, IDbConnection conn, IDbTransaction trans);

        protected abstract int GetTaskGroupId(string taskGroupName, IDbConnection conn, IDbTransaction trans);
        protected virtual int GenerateNextTaskGroupId()
        {
            return Interlocked.Increment(ref _taskGroupIdCounter);
        }

        public virtual int AddOrUpdateTaskGroup(string taskGroupName, string taskGroupDesc, int parent, string modifiedBy, string modifiedByHostName)
        {
            try
            {
                ChoGuard.ArgumentNotNullOrEmpty(taskGroupName, "TaskGroupName");
                ChoGuard.ArgumentNotNullOrEmpty(modifiedBy, "ModifiedBy");

                using (IDbConnection conn = Connection)
                {
                    conn.Open();
                    IDbTransaction trans = conn.BeginTransaction();

                    int taskGroupId = AddOrUpdateTaskGroup(taskGroupName, taskGroupDesc, parent, modifiedBy, modifiedByHostName, conn, trans);

                    trans.Commit();
                    return taskGroupId;
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;

            }
        }

        private int AddOrUpdateTaskGroup(string taskGroupName, string taskGroupDesc, int parent, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            int taskGroupId = GetTaskGroupId(taskGroupName, conn, trans);
            if (taskGroupId <= 0)
            {
                taskGroupId = GenerateNextTaskGroupId();
                taskGroupId = AddTaskGroup(taskGroupId, taskGroupName, taskGroupDesc, parent, modifiedBy, modifiedByHostName, conn, trans);
            }
            else
                taskGroupId = UpdateTaskGroup(taskGroupId, taskGroupName, taskGroupDesc, parent, modifiedBy, modifiedByHostName, conn, trans);

            return taskGroupId;
        }

        protected abstract int AddTaskGroup(int taskGroupId, string taskGroupName, string taskGroupDesc, int parent, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract int UpdateTaskGroup(int taskGroupId, string taskGroupName, string taskGroupDesc, int parent, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);

        public virtual void DeleteTaskGroup(string taskGroupName, string modifiedBy, string modifiedByHostName)
        {
            ChoGuard.ArgumentNotNullOrEmpty(taskGroupName, "TaskGroupName");
            ChoGuard.ArgumentNotNullOrEmpty(modifiedBy, "ModifiedBy");

            using (IDbConnection conn = Connection)
            {
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();

                try
                {
                    if (IsTaskGroupExists(taskGroupName))
                    {
                        ChoTasksDataSet.CHO_TASKSDataTable tasks = GetAllUITasks(null, taskGroupName, conn, trans);

                        //Delete all tasks under task group
                        foreach (ChoTasksDataSet.CHO_TASKSRow row in tasks.Rows)
                        {
                            if (!row.IsTASK_NAMENull())
                                DeleteTask(row.TASK_NAME, modifiedBy, modifiedByHostName, conn, trans);
                        }

                        //Delete all subgroups
                        foreach (string subGrpName in tasks.AsEnumerable().Select((r) => r.TASK_GRP_NAME.CastTo<string>()).Distinct())
                        {
                            if (subGrpName.IsNullOrWhiteSpace()) continue;

                            DeleteTaskGroup(subGrpName, modifiedBy, modifiedByHostName, conn, trans);
                            ChoTaskMgmtCommand cmd = new ChoTaskMgmtCommand()
                            {
                                TaskGroupName = subGrpName,
                                TaskMgmtType = ChoTaskChangeType.Remove
                            };
                            ChoDataModelEvents.RaiseTaskChanged(cmd, modifiedByHostName, modifiedBy);
                        }

                        //DeleteTaskGroup(taskGroupName, modifiedBy, modifiedByHostName, conn, trans);

                        trans.Commit();
                    }
                }
                catch (Exception ex)
                {
                    ChoTrace.Write(ex);
                    throw;
                }
            }
        }
        protected abstract void DeleteTaskGroup(string taskGroupName, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);

        public virtual bool IsTaskGroupExists(string taskGroupName)
        {
            try
            {
                ChoGuard.ArgumentNotNullOrEmpty(taskGroupName, "TaskGroupName");

                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    return IsTaskGroupExists(taskGroupName, conn, null);
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract bool IsTaskGroupExists(string taskGrpName, IDbConnection conn, IDbTransaction trans);

        public virtual ChoTasksDataSet.CHO_SCHEDULE_TYPESDataTable GetTaskScheduleTypes()
        {
            try
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    ChoTasksDataSet.CHO_SCHEDULE_TYPESDataTable table = new ChoTasksDataSet.CHO_SCHEDULE_TYPESDataTable();
                    table.Load(GetTaskScheduleTypes(conn, null));
                    return table;
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract IDataReader GetTaskScheduleTypes(IDbConnection conn, IDbTransaction trans);

        public virtual ChoTasksDataSet.CHO_TASK_SCHEDULESDataTable GetTaskSchedules(int taskId, string scheduleName)
        {
            using (IDbConnection conn = Connection)
            {
                conn.Open();

                try
                {
                    ChoTasksDataSet.CHO_TASK_SCHEDULESDataTable table = new ChoTasksDataSet.CHO_TASK_SCHEDULESDataTable();
                    table.Load(GetTaskSchedules(taskId, scheduleName, conn, null));
                    return table;
                }
                catch (Exception ex)
                {
                    ChoTrace.Write(ex);
                    throw;
                }
            }
        }
        protected abstract IDataReader GetTaskSchedules(int taskId, string scheduleName, IDbConnection conn, IDbTransaction trans);

        private void UpdateSchedule(int taskId, string taskName, int taskGrpId, string workflowName,
            ChoTasksDataSet.CHO_TASK_SCHEDULESDataTable taskScheduleInfo, string modifiedBy, string modifiedByHostName,
            IDbConnection conn, IDbTransaction trans)
        {
            if (taskScheduleInfo != null)
            {
                foreach (ChoTasksDataSet.CHO_TASK_SCHEDULESRow row in taskScheduleInfo.Rows)
                {
                    if (row.RowState == DataRowState.Added
                        || row.RowState == DataRowState.Modified)
                    //|| row.TASK_ID > 0 && taskId > 0 && row.TASK_ID != taskId)
                    {
                        row.TASK_ID = taskId;
                        row.START_TIME = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day,
                            row.START_TIME.Hour, row.START_TIME.Minute, row.START_TIME.Second);
                        if (!row.IsEND_TIMENull())
                            row.END_TIME = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day,
                                row.END_TIME.Hour, row.END_TIME.Minute, row.END_TIME.Second);
                    }
                }

                foreach (ChoTasksDataSet.CHO_TASK_SCHEDULESRow row in taskScheduleInfo.Rows)
                {
                    try
                    {
                        if (row.RowState == DataRowState.Added)
                        {
                            AddOrUpdateTaskSchedule(row, modifiedBy, modifiedByHostName, conn, trans);
                            AddOrUpdateTaskBatch(taskId, taskName, row.SCHEDULE_NAME, (int)Cinchoo.Core.ETL.ChoETLWorkflowStatus.INACTIVE, null, null, null, DateTime.MinValue, null, null,
                                null, 0, 0, modifiedBy, modifiedByHostName, conn, trans);
                        }
                        else if (row.RowState == DataRowState.Modified)
                        {
                            AddOrUpdateTaskSchedule(row, modifiedBy, modifiedByHostName, conn, trans);
                        }
                        else if (row.RowState == DataRowState.Deleted)
                        {
                            DeleteTaskSchedule(row[taskScheduleInfo.SCHEDULE_IDColumn, DataRowVersion.Original].CastTo<int>(), modifiedBy, modifiedByHostName, conn, trans);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new ApplicationException("Error while adding '{2}' task schedule info.{0}{1}.".FormatString(Environment.NewLine, ex.Message, row.SCHEDULE_NAME));
                    }
                }
            }
        }

        protected abstract int GetTaskScheduleId(string scheduleName, int taskId, IDbConnection conn, IDbTransaction trans);
        protected virtual int GenerateNextTaskScheduleId()
        {
            return Interlocked.Increment(ref _taskScheduleIdCounter);
        }

        private int AddOrUpdateTaskSchedule(ChoTasksDataSet.CHO_TASK_SCHEDULESRow row, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            if (row == null) return -1;

            int taskScheduleId = GetTaskScheduleId(row.SCHEDULE_NAME, row.TASK_ID, conn, trans);
            if (taskScheduleId <= 0)
            {
                taskScheduleId = GenerateNextTaskScheduleId();
                taskScheduleId = AddTaskSchedule(taskScheduleId, row, modifiedBy, modifiedByHostName, conn, trans);
            }
            else
                taskScheduleId = UpdateTaskSchedule(taskScheduleId, row, modifiedBy, modifiedByHostName, conn, trans);

            return taskScheduleId;
        }
        protected abstract int AddTaskSchedule(int taskScheduleId, ChoTasksDataSet.CHO_TASK_SCHEDULESRow row, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract int UpdateTaskSchedule(int taskScheduleId, ChoTasksDataSet.CHO_TASK_SCHEDULESRow row, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract void DeleteTaskSchedule(int taskScheduleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);

        public virtual void SetTaskStatus(int batchId, string taskName, string scheduleName, Cinchoo.Core.ETL.ChoETLWorkflowStatus taskStatus, string modifiedBy, string modifiedByHostName, string logMsg = null, string nextRunTime = null, string machineName = null)
        {
            AddOrUpdateTaskBatch(batchId, taskName, scheduleName, (int)taskStatus, null, machineName, logMsg, DateTime.MinValue, null, null,
                nextRunTime, Int32.MinValue, 0, modifiedBy, modifiedByHostName);
        }

        public virtual Cinchoo.Core.ETL.ChoETLWorkflowStatus GetTaskStatus(string taskName, string scheduleName)
        {
            ChoTaskGroupCategory TaskGroupCategory = new ChoTaskGroupCategory(0, taskName, scheduleName);
            ChoTaskModel.Refresh(TaskGroupCategory);

            return (Cinchoo.Core.ETL.ChoETLWorkflowStatus)TaskGroupCategory.TaskStatus;
        }

        public virtual int GetTaskExitCode(string taskName, string scheduleName)
        {
            ChoTaskGroupCategory TaskGroupCategory = new ChoTaskGroupCategory(0, taskName, scheduleName);
            ChoTaskModel.Refresh(TaskGroupCategory);

            return TaskGroupCategory.ExitCode;
        }

        protected abstract bool IsValidTaskStatus(int statusId, IDbConnection conn, IDbTransaction trans);
        protected abstract bool IsTaskBatchExists(int batchId, int taskId, int scheduleId, IDbConnection conn, IDbTransaction trans);

        public virtual void AddOrUpdateTaskBatch(int batchId, string taskName, string scheduleName, int taskStatus, string cmdParameters, string machineName, string logMsg, DateTime processStart,
            DateTime? processEnd, TimeSpan? duration, string nextRunTime, int exitCode, int? retryCount, string modifiedBy, string modifiedByHostName)
        {
            try
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();
                    IDbTransaction trans = conn.BeginTransaction();

                    AddOrUpdateTaskBatch(batchId, taskName, scheduleName, taskStatus,
                        cmdParameters, machineName, logMsg, processStart, processEnd, duration,
                        nextRunTime, exitCode, retryCount, modifiedBy, modifiedByHostName, conn, trans);

                    trans.Commit();
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }

        private void AddOrUpdateTaskBatch(int batchId, string taskName, string scheduleName, int? taskStatus,
            string cmdParameters, string machineName, string logMsg, DateTime processStart, DateTime? processEnd, TimeSpan? duration,
            string nextRunTime, int exitCode, int? retryCount, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            //Check if new task name exists
            if (!IsTaskExists(taskName, conn, trans))
                throw new ChoDbException("'{0}' task not exists.".FormatString(taskName));

            //Check valid taskstatus is passed
            if (taskStatus != null && !IsValidTaskStatus((int)taskStatus, conn, trans))
                throw new ChoDbException("Invalid '{0}' task status code.".FormatString(taskStatus));

            int taskId = GetTaskId(taskName, conn, trans);
            int scheduleId = GetTaskScheduleId(scheduleName, taskId, conn, trans);
            if (scheduleId == 0)
                throw new ChoDbException("No schedule setup for '{0}' task.".FormatString(taskName));

            //if (processStart == DateTime.MinValue)
            //    processStart = DateTime.Now;

            ChoTasksDataSet.CHO_TASKSDataTable taskInfo = GetAllUITasks(taskName, null, conn, trans);
            if (taskInfo != null && taskInfo.Rows.Count > 0)
            {
                ChoTasksDataSet.CHO_TASKSRow row = taskInfo[0] as ChoTasksDataSet.CHO_TASKSRow;

                if (cmdParameters.IsNull())
                    cmdParameters = row.IsBATCH_CMD_PARAMETERSNull() ? null : row.BATCH_CMD_PARAMETERS;
                if (machineName.IsNull())
                    machineName = row.IsBATCH_MACHINE_NAMENull() ? null : row.BATCH_MACHINE_NAME;
                if (logMsg.IsNull())
                    logMsg = row.IsLOG_MSGNull() ? null : row.LOG_MSG;
                if (processStart == DateTime.MinValue)
                    processStart = !row.IsSTART_TIMENull() ? row.START_TIME : ChoDbDateTime.MinValue;
                if (processEnd == null)
                    processEnd = row.IsEND_TIMENull() ? null : (DateTime?)row.END_TIME;
                if (duration == null)
                    duration = row.IsDURATIONNull() ? null : (TimeSpan?)TimeSpan.Parse(row.DURATION);
                if (nextRunTime == null)
                    nextRunTime = row.IsNEXT_RUN_TIMENull() ? null : row.NEXT_RUN_TIME;
                //else if (nextRunTime.Value == DateTime.MinValue)
                //    nextRunTime = null;

                if (retryCount == null)
                    retryCount = row.IsRETRY_COUNTNull() ? 0 : (int?)row.RETRY_COUNT;
                if (exitCode == Int32.MinValue)
                    exitCode = row.IsEXIT_CODENull() ? 0 : row.EXIT_CODE;
            }

            if (!IsTaskBatchExists(batchId, taskId, scheduleId, conn, trans))
            {
                if (taskStatus == null)
                    taskStatus = (int)ChoETLWorkflowStatus.INACTIVE;
                AddTaskBatch(batchId, taskId, scheduleId, taskStatus.Value, cmdParameters, machineName, logMsg, processStart, processEnd,
                    duration, nextRunTime, exitCode, retryCount, modifiedBy, modifiedByHostName, conn, trans);
            }
            else
            {
                ChoTasksDataSet.CHO_TASKSRow row1 = GetTask(taskName, scheduleName, conn, trans);
                if (row1 == null || row1.IsTASK_STATUSNull())
                {
                    if (taskStatus == null)
                        taskStatus = (int)ChoETLWorkflowStatus.INACTIVE;

                    UpdateTaskBatch(batchId, taskId, scheduleId, taskStatus.Value, cmdParameters, machineName, logMsg, processStart, processEnd,
                        duration, nextRunTime, exitCode, retryCount, modifiedBy, modifiedByHostName, conn, trans);
                }
                else
                {
                    ChoETLWorkflowStatus existingTaskStatus = (ChoETLWorkflowStatus)row1.TASK_STATUS;
                    ChoETLWorkflowStatus currentTaskStatus = taskStatus == null ? existingTaskStatus : (ChoETLWorkflowStatus)taskStatus.Value;

                    int currentBatchId = batchId;
                    int existingBatchId = row1.IsBATCH_IDNull() ? 0 : row1.BATCH_ID;

                    bool canUpdate = true;
                    if (currentTaskStatus == existingTaskStatus)
                    {
                    }
                    else
                    {
                        switch (currentTaskStatus)
                        {
                            case ChoETLWorkflowStatus.INACTIVE:
                                if (existingTaskStatus == ChoETLWorkflowStatus.RUNNING
                                    || existingTaskStatus == ChoETLWorkflowStatus.CLEAN)
                                    canUpdate = false;
                                else
                                    canUpdate = true;
                                break;
                            case ChoETLWorkflowStatus.ACTIVE:
                                if (existingTaskStatus == ChoETLWorkflowStatus.INVALID
                                    || existingTaskStatus == ChoETLWorkflowStatus.INACTIVE
                                    || existingTaskStatus == ChoETLWorkflowStatus.FAILED
                                    || existingTaskStatus == ChoETLWorkflowStatus.STOPPED
                                    || existingTaskStatus == ChoETLWorkflowStatus.SUCCESS
                                    || existingTaskStatus == ChoETLWorkflowStatus.ABORTED
                                    || existingTaskStatus == ChoETLWorkflowStatus.TIMEDOUT)
                                {
                                    //if (currentBatchId == existingBatchId) //Because resumetask uses same batchid
                                    //    canUpdate = false;
                                    //else
                                    canUpdate = true;
                                }
                                else
                                    canUpdate = false;
                                break;
                            case ChoETLWorkflowStatus.ABORTED:
                            case ChoETLWorkflowStatus.FAILED:
                            case ChoETLWorkflowStatus.STOPPED:
                            case ChoETLWorkflowStatus.SUCCESS:
                            case ChoETLWorkflowStatus.TIMEDOUT:
                                if (currentBatchId != existingBatchId)
                                    canUpdate = false;
                                break;
                            case ChoETLWorkflowStatus.RUNNING:
                                if (existingTaskStatus == ChoETLWorkflowStatus.ACTIVE)
                                {
                                    if (currentBatchId != existingBatchId)
                                        canUpdate = false;
                                    else
                                        canUpdate = true;
                                }
                                else
                                    canUpdate = false;
                                break;
                            default:
                                break;
                        }
                    }
                    if (canUpdate)
                        UpdateTaskBatch(batchId, taskId, scheduleId, (int)currentTaskStatus, cmdParameters, machineName, logMsg, processStart, processEnd,
                            duration, nextRunTime, exitCode, retryCount, modifiedBy, modifiedByHostName, conn, trans);
                    else
                        throw new ChoDbException("Can't update batch record [ExistingStatus:{0}, CurrentStatus:{1}] for task {2}/{3}.".FormatString(existingTaskStatus, currentTaskStatus, taskName, scheduleName));
                }
            }
        }

        private ChoTasksDataSet.CHO_TASKSRow GetTask(string taskName, string scheduleName, IDbConnection conn, IDbTransaction trans)
        {
            ChoTasksDataSet.CHO_TASKSDataTable tasks = GetAllUITasks(taskName, null, conn, trans);
            if (tasks == null || tasks.Rows.Count == 0)
                return null;

            foreach (ChoTasksDataSet.CHO_TASKSRow row in tasks.AsEnumerable().Where((r) => !r.IsSCHEDULE_NAMENull() && r.SCHEDULE_NAME == scheduleName))
                return row;

            return null;
        }

        protected abstract void AddTaskBatch(int batchId, int taskId, int scheduleId, int taskStatus,
            string cmdParameters, string machineName, string logMsg, DateTime processStart, DateTime? processEnd, TimeSpan? duration,
            string nextRunTime, int exitCode, int? retryCount, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract void UpdateTaskBatch(int batchId, int taskId, int scheduleId, int taskStatus,
            string cmdParameters, string machineName, string logMsg, DateTime processStart, DateTime? processEnd, TimeSpan? duration,
            string nextRunTime, int exitCode, int? retryCount, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract void DeleteTaskBatch(int scheduleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);

        public void ImportTasks(ChoTasksDataSet dsTasks, string modifiedBy, string machineName)
        {
            if (dsTasks == null) return;

            using (IDbConnection conn = Connection)
            {
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();

                try
                {
                    DateTime now = DateTime.Now;
                    ChoTasksDataSet.CHO_TASK_SCHEDULESDataTable taskSchedulesTable = new ChoTasksDataSet.CHO_TASK_SCHEDULESDataTable();
                    foreach (ChoTasksDataSet.CHO_TASKSRow taskRow in dsTasks.CHO_TASKS)
                    {
                        //Add to Task Group table
                        int taskGroupId = AddOrUpdateTaskGroup(taskRow.TASK_GRP_NAME, taskRow.IsTASK_GRP_DESCNull() ? null : taskRow.TASK_GRP_DESC,
                            taskRow.IsPARENTNull() ? 0 : taskRow.PARENT, modifiedBy, machineName, conn, trans);

                        if (taskRow.IsTASK_NAMENull() ||
                            taskRow.IsWORKFLOW_NAMENull())
                            continue;

                        //Import Task Schedules
                        taskSchedulesTable.Clear();
                        var x = from taskScheduleRow in dsTasks.CHO_TASK_SCHEDULES.AsEnumerable()
                                where taskScheduleRow.TASK_ID == taskRow.TASK_ID
                                select taskScheduleRow;

                        foreach (ChoTasksDataSet.CHO_TASK_SCHEDULESRow taskScheduleRow in x)
                        {
                            taskScheduleRow.CREATED_DATE = now;
                            taskScheduleRow.CREATED_BY = modifiedBy;
                            taskScheduleRow.MODIFIED_DATE = now;
                            taskScheduleRow.MODIFIED_BY = modifiedBy;
                            taskSchedulesTable.ImportRow(taskScheduleRow);
                        }

                        //Add to Task Table
                        AddOrUpdateTask(taskRow.TASK_NAME, taskRow.WORKFLOW_NAME,
                            taskRow.IsTASK_DESCNull() ? null : taskRow.TASK_DESC,
                            taskGroupId,
                            taskRow.IsCMD_PARAMETERSNull() ? null : taskRow.CMD_PARAMETERS,
                            taskRow.IsNO_OF_RETRYNull() ? null : (int?)taskRow.NO_OF_RETRY,
                            taskRow.IsSLEEP_BETWEEN_RETRYNull() ? null : (int?)taskRow.SLEEP_BETWEEN_RETRY,
                            taskRow.IsTIMEOUTNull() ? null : (int?)taskRow.TIMEOUT,
                            taskRow.IsMACHINE_NAMENull() ? null : taskRow.MACHINE_NAME,
                            taskSchedulesTable,
                            ChoUser.CurrentUser, Environment.MachineName, conn, trans);

                        ChoTaskMgmtCommand cmd = new ChoTaskMgmtCommand()
                        {
                            TaskGroupId = taskGroupId,
                            TaskMgmtType = ChoTaskChangeType.Update
                        };
                        ChoDataModelEvents.RaiseTaskChanged(cmd, machineName, modifiedBy);
                    }

                    trans.Commit();
                }
                catch (Exception ex)
                {
                    ChoTrace.Write(ex);
                    throw;
                }
            }
        }

        public virtual ChoTasksDataSet.CHO_HOLIDAY_CALENDARSDataTable GetAllHolidayCalendars()
        {
            using (IDbConnection conn = Connection)
            {
                conn.Open();

                try
                {
                    ChoTasksDataSet.CHO_HOLIDAY_CALENDARSDataTable table = new ChoTasksDataSet.CHO_HOLIDAY_CALENDARSDataTable();
                    table.Load(GetAllHolidayCalendars(conn, null));
                    return table;
                }
                catch (Exception ex)
                {
                    ChoTrace.Write(ex);
                    throw;
                }
            }
        }
        protected abstract IDataReader GetAllHolidayCalendars(IDbConnection conn, IDbTransaction trans);
        protected abstract string GetCalendarName(int calId, IDbConnection conn, IDbTransaction trans);

        private Dictionary<string, ChoTaskChangeType> GetModifiedCalendars(ChoTasksDataSet.CHO_HOLIDAY_CALENDARSDataTable calendarTable,
            ChoTasksDataSet.CHO_HOLIDAY_CALENDAR_DATESDataTable calendarDatesTable, IDbConnection conn, IDbTransaction trans)
        {
            Dictionary<string, ChoTaskChangeType> _updatedCals = new Dictionary<string, ChoTaskChangeType>();
            if (calendarTable != null)
            {
                foreach (ChoTasksDataSet.CHO_HOLIDAY_CALENDARSRow row in calendarTable.Rows)
                {
                    if (row.RowState == DataRowState.Added)
                    {
                        if (!_updatedCals.ContainsKey(row.CAL_NAME))
                            _updatedCals.Add(row.CAL_NAME, ChoTaskChangeType.Add);
                    }
                    else if (row.RowState == DataRowState.Modified)
                    {
                        if (!_updatedCals.ContainsKey(row.CAL_NAME))
                            _updatedCals.Add(row.CAL_NAME, ChoTaskChangeType.Update);
                    }
                    else
                    {
                        if (!_updatedCals.ContainsKey((string)row[calendarTable.CAL_NAMEColumn, DataRowVersion.Original]))
                            _updatedCals.Add((string)row[calendarTable.CAL_NAMEColumn, DataRowVersion.Original],
                                ChoTaskChangeType.Remove);
                    }
                }
            }

            if (calendarDatesTable != null)
            {
                foreach (ChoTasksDataSet.CHO_HOLIDAY_CALENDAR_DATESRow row in calendarDatesTable.Rows)
                {
                    if (row.RowState == DataRowState.Added)
                    {
                        string calName = GetCalendarName(row.CAL_ID, conn, trans);
                        if (calName != null && !_updatedCals.ContainsKey(calName))
                            _updatedCals.Add(calName, ChoTaskChangeType.Update);
                    }
                    else if (row.RowState == DataRowState.Modified)
                    {
                        string calName = GetCalendarName(row.CAL_ID, conn, trans);
                        if (calName != null && !_updatedCals.ContainsKey(calName))
                            _updatedCals.Add(calName, ChoTaskChangeType.Update);
                    }
                    else
                    {
                        string calName = GetCalendarName((int)row[calendarDatesTable.CAL_IDColumn, DataRowVersion.Original], conn, trans);
                        if (calName != null && !_updatedCals.ContainsKey(calName))
                            _updatedCals.Add(calName, ChoTaskChangeType.Update);
                    }
                }
            }

            return _updatedCals;
        }

        public virtual void UpdateHolidayCalendar(ChoTasksDataSet.CHO_HOLIDAY_CALENDARSDataTable calendarTable,
            ChoTasksDataSet.CHO_HOLIDAY_CALENDAR_DATESDataTable calendarDatesTable, string modifiedBy, string modifiedByHostName)
        {
            try
            {
                ChoGuard.ArgumentNotNullOrEmpty(modifiedBy, "ModifiedBy");

                using (IDbConnection conn = Connection)
                {
                    conn.Open();
                    IDbTransaction trans = conn.BeginTransaction();

                    Dictionary<string, ChoTaskChangeType> modifiedCals = GetModifiedCalendars(calendarTable, calendarDatesTable, conn, trans);

                    if (calendarTable != null)
                    {
                        foreach (ChoTasksDataSet.CHO_HOLIDAY_CALENDARSRow row in calendarTable.Rows)
                        {
                            if (row.RowState == DataRowState.Added)
                            {
                                row.CAL_ID = AddOrUpdateHolidayCalendar(row.CAL_NAME, row.IsCAL_DESCNull() ? null : row.CAL_DESC, modifiedBy,
                                    modifiedByHostName, conn, trans);
                            }
                            else if (row.RowState == DataRowState.Modified)
                            {
                                AddOrUpdateHolidayCalendar(row.CAL_NAME, row.CAL_DESC, modifiedBy, modifiedByHostName, conn, trans);
                            }
                            else
                            {
                                row.RejectChanges();
                                DeleteHolidayCalendar((string)row[calendarTable.CAL_NAMEColumn, DataRowVersion.Original], modifiedBy,
                                    modifiedByHostName, conn, trans);
                            }
                        }
                    }

                    if (calendarDatesTable != null)
                    {
                        foreach (ChoTasksDataSet.CHO_HOLIDAY_CALENDAR_DATESRow row in calendarDatesTable.Rows)
                        {
                            if (row.RowState == DataRowState.Added
                                || row.RowState == DataRowState.Modified)
                                AddOrUpdateHolidayCalendarDate(row.CAL_ID, row.CAL_DATE, row.IsCAL_DATE_DESCNull() ? null : row.CAL_DATE_DESC,
                                    modifiedBy, modifiedByHostName, conn, trans);
                            else
                            {
                                row.RejectChanges();
                                DeleteHolidayCalendarDate((int)row[calendarDatesTable.CAL_IDColumn, DataRowVersion.Original],
                                    (DateTime)row[calendarDatesTable.CAL_DATEColumn, DataRowVersion.Original], modifiedBy, modifiedByHostName,
                                    conn, trans);
                            }
                        }
                    }

                    trans.Commit();

                    foreach (string calName in modifiedCals.Keys)
                        ChoDataModelEvents.RaiseCalendarChanged(new ChoCalendarCommand() { CalendarName = calName, OperationType = modifiedCals[calName] }, modifiedByHostName, modifiedBy);
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }

        protected abstract int GetHolidayCalendarId(string calName, IDbConnection conn, IDbTransaction trans);
        protected virtual int GenerateNextHolidayCalendarId()
        {
            return Interlocked.Increment(ref _holidayCalIdCounter);
        }

        protected int AddOrUpdateHolidayCalendar(string calName, string calDesc, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            if (calName.IsNullOrWhiteSpace()) return -1;

            int holidayCalIdCounter = GetHolidayCalendarId(calName, conn, trans);
            if (holidayCalIdCounter > 0)
                holidayCalIdCounter = UpdateHolidayCalendar(holidayCalIdCounter, calName, calDesc, modifiedBy, modifiedByHostName, conn, trans);
            else
            {
                holidayCalIdCounter = GenerateNextHolidayCalendarId();
                holidayCalIdCounter = AddHolidayCalendar(holidayCalIdCounter, calName, calDesc, modifiedBy, modifiedByHostName, conn, trans);
            }
            return holidayCalIdCounter;
        }
        protected abstract int AddHolidayCalendar(int calId, string calName, string calDesc, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract int UpdateHolidayCalendar(int calId, string calName, string calDesc, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract int DeleteHolidayCalendar(string calName, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);

        protected abstract bool IsHolidayCalendarDateExists(int calId, DateTime calDate, IDbConnection conn, IDbTransaction trans);
        protected void AddOrUpdateHolidayCalendarDate(int calId, DateTime calDate, string calDateDesc, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            bool isExists = IsHolidayCalendarDateExists(calId, calDate, conn, trans);
            if (!isExists)
                AddHolidayCalendarDate(calId, calDate, calDateDesc, modifiedBy, modifiedByHostName, conn, trans);
            else
                UpdateHolidayCalendarDate(calId, calDate, calDateDesc, modifiedBy, modifiedByHostName, conn, trans);
        }
        protected abstract void AddHolidayCalendarDate(int calId, DateTime calDate, string calDateDesc, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract void UpdateHolidayCalendarDate(int calId, DateTime calDate, string calDateDesc, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract void DeleteHolidayCalendarDate(int calId, DateTime calDate, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);

        public virtual ChoTasksDataSet.CHO_HOLIDAY_CALENDAR_DATESDataTable GetAllHolidayCalendarDatesById(int holidayCalId)
        {
            using (IDbConnection conn = Connection)
            {
                conn.Open();

                try
                {
                    ChoTasksDataSet.CHO_HOLIDAY_CALENDAR_DATESDataTable table = new ChoTasksDataSet.CHO_HOLIDAY_CALENDAR_DATESDataTable();
                    table.Load(GetAllHolidayCalendarDatesById(holidayCalId, conn, null));
                    return table;
                }
                catch (Exception ex)
                {
                    ChoTrace.Write(ex);
                    throw;
                }
            }
        }
        protected abstract IDataReader GetAllHolidayCalendarDatesById(int holidayCalId, IDbConnection conn, IDbTransaction trans);

        public virtual ChoTasksDataSet.CHO_HOLIDAY_CALENDAR_DATESDataTable GetAllHolidayCalendarDatesByName(string holidayCalName)
        {
            using (IDbConnection conn = Connection)
            {
                conn.Open();

                try
                {
                    ChoTasksDataSet.CHO_HOLIDAY_CALENDAR_DATESDataTable table = new ChoTasksDataSet.CHO_HOLIDAY_CALENDAR_DATESDataTable();
                    table.Load(GetAllHolidayCalendarDatesByName(holidayCalName, conn, null));
                    return table;
                }
                catch (Exception ex)
                {
                    ChoTrace.Write(ex);
                    throw;
                }
            }
        }
        protected abstract IDataReader GetAllHolidayCalendarDatesByName(string holidayCalName, IDbConnection conn, IDbTransaction trans);

        public virtual int CloneCalendar(string calName, string newCalName, string modifiedBy, string modifiedByHostName)
        {
            int newCalId = -1;

            try
            {
                ChoGuard.ArgumentNotNullOrEmpty(calName, "CalName");
                ChoGuard.ArgumentNotNullOrEmpty(newCalName, "NewCalName");
                ChoGuard.ArgumentNotNullOrEmpty(modifiedBy, "ModifiedBy");

                using (IDbConnection conn = Connection)
                {
                    conn.Open();
                    IDbTransaction trans = conn.BeginTransaction();

                    try
                    {
                        //Check if new cal exists
                        if (GetHolidayCalendarId(newCalName, conn, trans) > 0)
                            throw new ChoDbException("'{0}' holiday calendar already exists.".FormatString(newCalName));

                        if (GetHolidayCalendarId(calName, conn, trans) <= 0)
                            throw new ChoDbException("'{0}' holiday calendar not exists.".FormatString(calName));

                        int calId = AddOrUpdateHolidayCalendar(newCalName, "{0} Copy".FormatString(calName), modifiedBy, modifiedByHostName, conn, trans);

                        ChoTasksDataSet.CHO_HOLIDAY_CALENDAR_DATESDataTable calDatesTable = GetAllHolidayCalendarDatesByName(calName);
                        foreach (ChoTasksDataSet.CHO_HOLIDAY_CALENDAR_DATESRow row in calDatesTable)
                            AddOrUpdateHolidayCalendarDate(calId, row.CAL_DATE, row.IsCAL_DATE_DESCNull() ? null : row.CAL_DATE_DESC, modifiedBy, modifiedByHostName, conn, trans);

                        trans.Commit();

                        ChoDataModelEvents.RaiseCalendarChanged(new ChoCalendarCommand() { CalendarName = newCalName, OperationType = ChoTaskChangeType.Add }, modifiedByHostName, modifiedBy);

                    }
                    catch (Exception ex)
                    {
                        ChoTrace.Write(ex);
                        throw;
                    }
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }

            return newCalId;
        }

        public virtual ChoTasksDataSet.CHO_POLICYDataTable GetAllPolicy()
        {
            try
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    ChoTasksDataSet.CHO_POLICYDataTable table = new ChoTasksDataSet.CHO_POLICYDataTable();

                    table.Load(GetAllPolicy(conn, null));
                    return table;
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract IDataReader GetAllPolicy(IDbConnection conn, IDbTransaction trans);

        public virtual ChoTasksDataSet.CHO_USERSDataTable GetAllUsers()
        {
            try
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    ChoTasksDataSet.CHO_USERSDataTable table = new ChoTasksDataSet.CHO_USERSDataTable();
                    table.Load(GetAllUsers(conn, null));
                    return table;
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract IDataReader GetAllUsers(IDbConnection conn, IDbTransaction trans);

        public virtual void UpdateUsers(ChoTasksDataSet.CHO_USERSDataTable usersTable, string modifiedBy, string modifiedByHostName)
        {
            try
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();
                    IDbTransaction trans = conn.BeginTransaction();

                    if (usersTable != null)
                    {
                        foreach (ChoTasksDataSet.CHO_USERSRow row in usersTable.Rows)
                        {
                            if (row.RowState == DataRowState.Added
                                || row.RowState == DataRowState.Modified)
                            {
                                AddOrUpdateUser(row.NAME, row.IsFULL_NAMENull() ? null : row.FULL_NAME,
                                    row.IsPASSWORDNull() ? null : row.PASSWORD, row.ENABLED, modifiedBy, modifiedByHostName, conn, trans);
                            }
                            else
                            {
                                DeleteUser((int)row[usersTable.IDColumn, DataRowVersion.Original], modifiedBy, modifiedByHostName, conn, trans);
                            }
                        }

                        trans.Commit();

                        ChoDataModelEvents.RaiseUserACLChanged(new ChoUsersACLChangedCommand(), modifiedByHostName, modifiedBy);
                    }
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }

        protected abstract int GetUserId(string name, IDbConnection conn, IDbTransaction trans);

        protected virtual int GenerateNextUserId()
        {
            return Interlocked.Increment(ref _userId);
        }

        private int AddOrUpdateUser(string name, string fullName, string password, bool enabled, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            int userId = GetUserId(name, conn, trans);
            if (userId > 0)
                userId = UpdateUser(userId, name, fullName, password, enabled, modifiedBy, modifiedByHostName, conn, trans);
            else
            {
                userId = Interlocked.Increment(ref _userId);
                userId = AddUser(userId, name, fullName, password, enabled, modifiedBy, modifiedByHostName, conn, trans);
            }
            return userId;
        }

        protected abstract int AddUser(int id, string name, string fullName, string password, bool enabled, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract int UpdateUser(int id, string name, string fullName, string password, bool enabled, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract void DeleteUser(int id, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);

        public virtual ChoTasksDataSet.CHO_ROLESDataTable GetAllRoles()
        {
            try
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    ChoTasksDataSet.CHO_ROLESDataTable table = new ChoTasksDataSet.CHO_ROLESDataTable();

                    table.Load(GetAllRoles(conn, null));
                    return table;
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract IDataReader GetAllRoles(IDbConnection conn, IDbTransaction trans);

        public virtual ChoTasksDataSet.CHO_ROLE_USERSDataTable GetAllUserRoles(int roleId)
        {
            try
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    ChoTasksDataSet.CHO_ROLE_USERSDataTable table = new ChoTasksDataSet.CHO_ROLE_USERSDataTable();

                    table.Load(GetAllUserRoles(roleId, conn, null));
                    return table;
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract IDataReader GetAllUserRoles(int roleId, IDbConnection conn, IDbTransaction trans);

        public virtual ChoTasksDataSet.CHO_ROLE_POLICYDataTable GetAllRolePolicy(int roleId)
        {
            try
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    ChoTasksDataSet.CHO_ROLE_POLICYDataTable table = new ChoTasksDataSet.CHO_ROLE_POLICYDataTable();

                    table.Load(GetAllRolePolicy(roleId, conn, null));
                    return table;
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract IDataReader GetAllRolePolicy(int roleId, IDbConnection conn, IDbTransaction trans);

        public virtual void UpdateRoles(ChoTasksDataSet.CHO_ROLESDataTable rolesTable, ChoTasksDataSet.CHO_ROLE_USERSDataTable userRolesTable,
            ChoTasksDataSet.CHO_ROLE_POLICYDataTable rolesPolicyTable, string modifiedBy, string modifiedByHostName)
        {
            try
            {
                using (IDbConnection conn = Connection)
                {
                    conn.Open();
                    IDbTransaction trans = conn.BeginTransaction();

                    if (rolesTable != null)
                    {
                        foreach (ChoTasksDataSet.CHO_ROLESRow row in rolesTable.Rows)
                        {
                            if (row.RowState == DataRowState.Added
                                || row.RowState == DataRowState.Modified)
                            {
                                int roleId = AddOrUpdateRole(row.NAME, row.IsCOMMENTNull() ? null : row.COMMENT, row.ENABLED, modifiedBy,
                                    modifiedByHostName, conn, trans);

                                if (userRolesTable != null)
                                {
                                    //Update to userRolesTable
                                    foreach (ChoTasksDataSet.CHO_ROLE_USERSRow userRow in
                                        (from x in userRolesTable.AsEnumerable()
                                         where x.NAME == row.NAME
                                         select x))
                                    {
                                        userRow.ID = roleId;
                                    }
                                }
                                if (rolesPolicyTable != null)
                                {
                                    //Update to rolePolicyTable
                                    foreach (ChoTasksDataSet.CHO_ROLE_POLICYRow policyRow in
                                                (from x in rolesPolicyTable.AsEnumerable()
                                                 where x.NAME == row.NAME
                                                 select x))
                                    {
                                        policyRow.ID = roleId;
                                    }
                                }
                            }
                            else
                            {
                                DeleteRole((int)row[rolesTable.IDColumn, DataRowVersion.Original], modifiedBy, modifiedByHostName, conn, trans);
                            }
                        }
                    }

                    if (userRolesTable != null)
                    {
                        foreach (ChoTasksDataSet.CHO_ROLE_USERSRow row in userRolesTable.Rows)
                        {
                            if (row.RowState == DataRowState.Added
                                || row.RowState == DataRowState.Modified)
                            {
                                AddUserRoleInternal(row.USER_ID, row.ID, modifiedBy, modifiedByHostName, conn, trans);
                            }
                            else
                            {
                                //row.RejectChanges();
                                DeleteUserRole((int)row[userRolesTable.USER_IDColumn, DataRowVersion.Original],
                                    (int)row[userRolesTable.IDColumn, DataRowVersion.Original], modifiedBy, modifiedByHostName, conn, trans);
                            }
                        }
                    }

                    if (rolesPolicyTable != null)
                    {
                        foreach (ChoTasksDataSet.CHO_ROLE_POLICYRow row in rolesPolicyTable.Rows)
                        {
                            if (row.RowState == DataRowState.Added
                                || row.RowState == DataRowState.Modified)
                            {
                                AddRolePolicyInternal(row.ID, row.POLICY_ID, modifiedBy, modifiedByHostName, conn, trans);
                            }
                            else
                            {
                                //row.RejectChanges();
                                DeleteRolePolicy((int)row[rolesPolicyTable.IDColumn, DataRowVersion.Original],
                                    (int)row[rolesPolicyTable.POLICY_IDColumn, DataRowVersion.Original], modifiedBy, modifiedByHostName, conn, trans);
                            }
                        }
                    }

                    trans.Commit();

                    ChoDataModelEvents.RaiseUserACLChanged(new ChoUsersACLChangedCommand(), modifiedByHostName, modifiedBy);
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }

        protected abstract int GetRoleId(string name, IDbConnection conn, IDbTransaction trans);

        protected virtual int GenerateNextRoleId()
        {
            return Interlocked.Increment(ref _roleId);
        }

        private int AddOrUpdateRole(string name, string comment, bool enabled, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            int roleId = GetRoleId(name, conn, trans);
            if (roleId > 0)
                roleId = UpdateRole(roleId, name, comment, enabled, modifiedBy, modifiedByHostName, conn, trans);
            else
            {
                roleId = Interlocked.Increment(ref _userId);
                roleId = AddRole(roleId, name, comment, enabled, modifiedBy, modifiedByHostName, conn, trans);
            }
            return roleId;
        }
        protected abstract int UpdateRole(int id, string name, string comment, bool enabled, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract int AddRole(int id, string name, string comment, bool enabled, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract void DeleteRole(int roleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);

        protected abstract bool IsRolePolicyExists(int roleId, int policyId, IDbConnection conn, IDbTransaction trans);
        private void AddRolePolicyInternal(int roleId, int policyId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            if (IsRolePolicyExists(roleId, policyId, conn, trans)) return;
            AddRolePolicy(roleId, policyId, modifiedBy, modifiedByHostName, conn, trans);
        }
        protected abstract void AddRolePolicy(int roleId, int policyId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract void DeleteRolePolicy(int roleId, int policyId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract void DeleteRolePolicyForRole(int roleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);

        private void AddUserRoleInternal(int userId, int roleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            DeleteUserRole(userId, modifiedBy, modifiedByHostName, conn, trans);
            AddUserRole(userId, roleId, modifiedBy, modifiedByHostName, conn, trans);
        }
        protected abstract void AddUserRole(int userId, int roleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract void DeleteUserRole(int userId, int roleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract void DeleteUserRole(int userId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);
        protected abstract void DeleteUserRoleForRole(int roleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans);

        public virtual ChoTasksDataSet.CHO_POLICYDataTable GetPolicyForUser(string userName)
        {
            try
            {
                ChoGuard.ArgumentNotNullOrEmpty(userName, "UserName");

                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    ChoTasksDataSet.CHO_POLICYDataTable table = new ChoTasksDataSet.CHO_POLICYDataTable();
                    table.Load(GetPolicyForUser(userName, conn, null));
                    return table;
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract IDataReader GetPolicyForUser(string userName, IDbConnection conn, IDbTransaction trans);

        public virtual ChoTasksDataSet.CHO_DEPENDENCY_TASKSDataTable GetAllDepedencyTasks(string jobName)
        {
            try
            {
                ChoGuard.ArgumentNotNullOrEmpty(jobName, "JobName");

                using (IDbConnection conn = Connection)
                {
                    conn.Open();

                    ChoTasksDataSet.CHO_DEPENDENCY_TASKSDataTable table = new ChoTasksDataSet.CHO_DEPENDENCY_TASKSDataTable();
                    table.Load(GetAllDepedencyTasks(jobName, conn, null));
                    return table;
                }
            }
            catch (Exception ex)
            {
                ChoTrace.Write(ex);
                throw;
            }
        }
        protected abstract IDataReader GetAllDepedencyTasks(string jobName, IDbConnection conn, IDbTransaction trans);

        public abstract IDbConnection Connection
        {
            get;
        }

        protected abstract IDbCommand CreateCommand(string cmdText, IDbConnection conn, IDbTransaction trans = null, CommandType cmdType = CommandType.Text);
        protected abstract DbParameter CreateParameter(string paramName, object paramValue = null);

        public KeyValuePair<string, string>[] GetClusterNodePairs()
        {
            return ChoClusterManager.Default.AllClusterNodePairs;
        }

        public byte[] DownloadWFPlugIn(string wfPlugInRelativeFilePath)
        {
            if (String.IsNullOrWhiteSpace(wfPlugInRelativeFilePath))
                return null;

            return ChoFile.ReadAllBytes(Path.Combine(ChoWFPlugInsFolderSynchronizer.Value.BaseDir, wfPlugInRelativeFilePath));
        }

        public ChoFSFileInfo[] GetWFPlugInsFileList()
        {
            return ChoWFPlugInsFolderSynchronizer.Value.GetFileList();
        }

        public byte[] DownloadWFDef(string wfDefRelativeFilePath)
        {
            if (String.IsNullOrWhiteSpace(wfDefRelativeFilePath))
                return null;

            return ChoFile.ReadAllBytes(Path.Combine(ChoWFDefsFolderSynchronizer.Value.BaseDir, wfDefRelativeFilePath));
        }

        public ChoFSFileInfo[] GetWFDefsFileList()
        {
            return ChoWFDefsFolderSynchronizer.Value.GetFileList();
        }

        public byte[] DownloadWFData(string wfDataRelativeFilePath)
        {
            if (String.IsNullOrWhiteSpace(wfDataRelativeFilePath))
                return null;

            return ChoFile.ReadAllBytes(Path.Combine(ChoWFDataFolderSynchronizer.Value.BaseDir, wfDataRelativeFilePath));
        }

        public ChoFSFileInfo[] GetWFDataFileList()
        {
            return ChoWFDataFolderSynchronizer.Value.GetFileList();
        }

        public void FireDependentTasks(int batchId, string taskName, string scheduleName, string user, string originHostName)
        {
            IChoWorkflowScheduler workflowScheduler = WorkflowScheduler;

            if (workflowScheduler != null)
                workflowScheduler.FireDependentTasks(batchId, taskName, scheduleName, user, originHostName);
        }

        public bool IsUserPolicyEnabled()
        {
            ChoWFServerCmdLineArgsObject x = new ChoWFServerCmdLineArgsObject();
            return x.EnableUsers;
        }

        public abstract void RefreshDB();

        public IChoWorkflowScheduler WorkflowScheduler
        {
            get;
            set;
        }
    }
}
